package data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import connexionBd.ConnexionBd;
import data.bean.Releve;

public class ReleveDAO {

	private final static Connection connexion = ConnexionBd.getConnection();
	private static String requeteInsert = "INSERT INTO energie.releve "
					+ "(id_Maison, id_Equipement,date_Heure, state, mesure_energie) "
					+ "VALUES ";

	public static String getRequeteInsert() {
		return requeteInsert;
	}

	public static Releve select(int idMaison, int idEquipement, String dateHeure) {
		Releve releve = null;
		try {
			String sql = "SELECT * FROM energie.releve "
					+ "WHERE id_Maison = ? AND id_Equipement = ? AND date_Heure = STR_TO_DATE(?,'%d/%m/%y %H:%i')";
			
			PreparedStatement preparedStmt = connexion.prepareStatement(sql);
			preparedStmt.setInt(1, idMaison);
			preparedStmt.setInt(2, idEquipement);
			preparedStmt.setString(3, dateHeure);
			
			String statementText = preparedStmt.toString();
			
			ResultSet result = preparedStmt.executeQuery();
			if (result.first()) {
				releve = new Releve(idMaison, idEquipement, dateHeure, 
						result.getInt("state"), result.getInt("mesure_energie"));
			}
			
			result.close();
			preparedStmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return releve;
	}

	public static boolean insert(Releve releve) {
		boolean result = false;
		try {
			String sql = "INSERT INTO energie.releve "
					+ "(id_Maison, id_Equipement, date_Heure, state, mesure_energie) "
					+ "VALUES (?,?,STR_TO_DATE(?,'%d/%m/%y %H:%i'),?,?)";
			
			PreparedStatement preparedStmt = connexion.prepareStatement(sql);
			preparedStmt.setInt(1, releve.getIdMaison());
			preparedStmt.setInt(2, releve.getIdEquipement());
			preparedStmt.setString(3, releve.getDateHeure());
			preparedStmt.setInt(4, releve.getState());
			preparedStmt.setInt(5, releve.getMesure());
			
			String statementText = preparedStmt.toString();

			int statut = preparedStmt.executeUpdate();
			result = (statut == 1);
			
			preparedStmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static String getRequeteInsert(Releve releve) {
			String sql = "INSERT INTO energie.releve "
					+ "(id_Maison, id_Equipement,date_Heure, state, mesure_energie) "
					+ "VALUES ("
					+ releve.getIdMaison() + ","
					+ releve.getIdEquipement() +","
					+ "STR_TO_DATE('" +releve.getDateHeure() +"','%d/%m/%y %H:%i'),"
					+ releve.getState() +","
					+ releve.getMesure() +")";
			
		return sql;
	}
	
	public static String getRequeteInsertPerf(Releve releve) {
		String sql = "("
				+ releve.getIdMaison() + ","
				+ releve.getIdEquipement() +","
				+ "STR_TO_DATE('" +releve.getDateHeure() +"','%d/%m/%y %H:%i'),"
				+ releve.getState() +","
				+ releve.getMesure() +"),";
		
	return sql;
}


	public static boolean delete(int idMaison, int idEquipement, String dateHeure) {
		boolean result = false;
		try {
			String sql = "DELETE FROM energie.releve "
					+ "WHERE id_Maison = ? AND id_Equipement = ? AND date_Heure = STR_TO_DATE(?,'%d/%m/%y %H:%i')";

			PreparedStatement preparedStmt = connexion.prepareStatement(sql);
			preparedStmt.setInt(1, idMaison);
			preparedStmt.setInt(2, idEquipement);
			preparedStmt.setString(3, dateHeure);
			
			String statementText = preparedStmt.toString();

			int statut = preparedStmt.executeUpdate();
			result = (statut == 1);
			
			preparedStmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
